package cmcc.dbi.execJdbcUtil;

import cmcc.dbi.dto.DbQueryDTO;
import cmcc.dbi.entity.InspectionItemRecord;
import cmcc.dbi.execEnum.DbTypeOfDbNameEnum;
import cmcc.dbi.execEnum.DbTypeOfDriverEnum;
import cmcc.dbi.execEnum.DbTypeOfUrlEnum;
import cmcc.dbi.execUtil.AESEncryptionUtil;
import com.alibaba.fastjson.JSONArray;
import com.alibaba.fastjson.JSONObject;
import lombok.extern.slf4j.Slf4j;

import java.sql.*;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

@Slf4j
public class MysqlOfJdbcUtil implements JdbcUtil{
    @Override
    public JSONArray getTargetResult(List<InspectionItemRecord> scriptsLists, DbQueryDTO dbQueryDTO){
        Connection con;

        //根据数据库类别从枚举类中活得对应的默认库和url
        String theDatName = DbTypeOfDbNameEnum.match(dbQueryDTO.getDbType()).getDbName();
        // 替换默认的url构建连接
        String url = DbTypeOfUrlEnum.match(dbQueryDTO.getDbType()).getDbUrl().replace("DBIP", dbQueryDTO.getDbIP())
                .replace("DBPORT", dbQueryDTO.getDbPort()).replace("DBNAME",theDatName);

//        String url = "jdbc:mysql://" + dbQueryDTO.getDbIP() + ":" + dbQueryDTO.getDbPort() + "/mysql?useUnicode=true&characterEncoding=utf-8&serverTimezone=Asia/Shanghai&allowMultiQueries=true";
        Statement psql;
        JSONArray queryAllResult = new JSONArray();
        //List<Map<String, List<Map<String, Object>>>> queryResult = new ArrayList<>();

        try{
            //加载驱动
            String dbDriver = DbTypeOfDriverEnum.match(dbQueryDTO.getDbType()).getDbDriver();
            Class.forName(dbDriver);
            //构建连接
            //con = DriverManager.getConnection(url, dbQueryDTO.getDbUser(), dbQueryDTO.getDbPass());
            String pass = AESEncryptionUtil.decrypt(dbQueryDTO.getDbPass());
            log.info("连接信息: "+dbQueryDTO.getDbIP() + " - " + dbQueryDTO.getDbPort()+" - "+dbQueryDTO.getDbUser()+
                    " - "+dbQueryDTO.getDbPass());
            con = DriverManager.getConnection(url, dbQueryDTO.getDbUser(), pass);

            if (con != null) {
                log.info(dbQueryDTO.getDbIP() + "的 MySQL 数据库连接成功");
                // 用于执行静态SQL语句并返回其产生的结果的对象
                psql = con.createStatement();

                for(int i=0; i< scriptsLists.size(); i++){
                    log.info("The script is -> {}", scriptsLists.get(i).getItemName());
                    JSONObject querySingleResult = new JSONObject();
                    try{
                        ResultSet resultSet = psql.executeQuery(scriptsLists.get(i).getScript());
                        if(!resultSet.next()){
                            log.info("The result is null");
                            querySingleResult.put("item_id", scriptsLists.get(i).getItemId());
                            querySingleResult.put("jdbc_result", null);
                        }else{
                            do{
                                List<Map<String, Object>> resultOfList = new ArrayList<>();
                                for (int j = 1; j <= resultSet.getMetaData().getColumnCount(); j++) {
                                    Map<String, Object> tmp = new HashMap<>();
                                    Object value = resultSet.getObject(j);
                                    tmp.put(resultSet.getMetaData().getColumnLabel(j), value);
                                    resultOfList.add(tmp);
                                }
                                log.info("The result of script execution is -> {}", resultOfList);
                                querySingleResult.put("item_id", scriptsLists.get(i).getItemId());
                                querySingleResult.put("jdbc_result", resultOfList);
                            }while(resultSet.next());
                        }
                    }catch (SQLException e){
                        log.info("脚本出错，请检查");
                        System.out.println(e);
                        List<Map<String, Object>> resultOfList = new ArrayList<>();
                        Map<String, Object> tmp = new HashMap<>();
                        tmp.put("error_info", e.getMessage());
                        resultOfList.add(tmp);
                        querySingleResult.put("item_id", scriptsLists.get(i).getItemId());
                        querySingleResult.put("jdbc_result", resultOfList);
                    }
                    queryAllResult.add(querySingleResult);
                }

                psql.close();
                con.close();
            }
        } catch (ClassNotFoundException | SQLException e) {
            log.error(dbQueryDTO.getDbIP() + " 的 MySQL 数据库执行巡检脚本失败 " + e.getMessage());
        }
        return queryAllResult;
    }

    private static JSONArray OnceInspectedAllSql(List<InspectionItemRecord> scriptsLists, DbQueryDTO dbQueryDTO){
        Connection con = null;
        String url = "jdbc:mysql://" + dbQueryDTO.getDbIP() + ":" + dbQueryDTO.getDbPort() + "/mysql?useUnicode=true&characterEncoding=utf-8&serverTimezone=Asia/Shanghai&allowMultiQueries=true";
        Statement psql = null;
        JSONArray queryAllResult = new JSONArray();

        try{
            //加载驱动
            String dbDriver = DbTypeOfDriverEnum.match(dbQueryDTO.getDbType()).getDbDriver();
            Class.forName(dbDriver);
            //构建连接
            con = DriverManager.getConnection(url, dbQueryDTO.getDbUser(), dbQueryDTO.getDbPass());

            if (con != null) {
                log.info("mysql数据库连接成功");
                // 用于执行静态SQL语句并返回其产生的结果的对象
                psql = con.createStatement();
                StringBuilder mergeSql = new StringBuilder();
                for(int i=0; i<scriptsLists.size(); i++){
                    mergeSql.append(scriptsLists.get(i).getScript());
                    mergeSql.append("\n");
                }
                log.info("The scripts -> {}", mergeSql);

                Boolean hasResult = psql.execute(String.valueOf(mergeSql));
                int count = 0;

                while (hasResult) {
                    JSONObject querySingleResult = new JSONObject();
                    ResultSet rs = psql.getResultSet();
                    while (rs.next()) {
                        List<Map<String, Object>> lisTmp = new ArrayList<>();
                        for (int i = 1; i <= rs.getMetaData().getColumnCount(); i++) {
                            Map<String, Object> tmp = new HashMap<>();
                            Object value = rs.getObject(i);
                            tmp.put(rs.getMetaData().getColumnLabel(i), value);
                            lisTmp.add(tmp);
                        }
                        querySingleResult.put("item_id", scriptsLists.get(count).getItemId());
                        querySingleResult.put("jdbc_result", lisTmp);
                        //queryTmp.put(re.get(count).get("name"), lisTmp);
                    }
                    queryAllResult.add(querySingleResult);
                    count++;
                    hasResult = psql.getMoreResults();
                }

                psql.close();
                con.close();
            }
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return queryAllResult;

    }
}
